﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class FcDAL:IFcDAL
    {
        public int AddFc(FcInfo fc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, fc.Num),
                DALHelper.MakeInParam(":fday", OracleType.VarChar, fc.Fday),
                DALHelper.MakeInParam(":cxh", OracleType.VarChar, fc.Cxh),
                DALHelper.MakeInParam(":dun", OracleType.Number, fc.Dun),
                DALHelper.MakeInParam(":fcy", OracleType.VarChar, fc.Fcy),
                DALHelper.MakeInParam(":jt", OracleType.VarChar, fc.Jt),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, fc.Ku),
                DALHelper.MakeInParam(":cf", OracleType.Number, fc.Cf),
                DALHelper.MakeInParam(":js", OracleType.Number, fc.Js)

            };
            return (DALHelper.RunCommand(@"insert into dd.FC (num, fday, cxh, dun, fcy, jt, ku, cf, js) values (:num, to_date(:fday,'yyyy-mm-dd hh24:mi:ss'), :cxh, :dun, :fcy, :jt, :ku, :cf, :js)", prams));
        }
        public int UpdateFc(string num, string cxh, decimal dun, string fcy, string jt, string ku, decimal cf, decimal js, string info)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                
                DALHelper.MakeInParam(":cxh", OracleType.VarChar, cxh),
                DALHelper.MakeInParam(":dun", OracleType.Number, dun),
                DALHelper.MakeInParam(":fcy", OracleType.VarChar, fcy),
                DALHelper.MakeInParam(":jt", OracleType.VarChar, jt),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":cf", OracleType.Number, cf),
                DALHelper.MakeInParam(":js", OracleType.Number, js),
                DALHelper.MakeInParam(":info", OracleType.VarChar, info)

            };
            return (DALHelper.RunCommand(@"update dd.Fc set cxh = :cxh, dun = :dun, fcy = :fcy, jt = :jt, ku = :ku, cf = :cf, js = :js, info = :info where num = :num", prams));
        }
        public int UpdateFcBZ(string num, string info)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":info", OracleType.VarChar, info)

            };
            return (DALHelper.RunCommand(@"update dd.Fc set info = :info where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.FC where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.FC where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday, string ku)
        {
            string bday = sday + "00:00:00";
            string eday = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bday", OracleType.VarChar, bday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, eday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };

            return (DALHelper.RunProcReturn(@"select * from dd.FC where fday >= to_date(:bday,'yyyy-mm-dd hh24:mi:ss') and fday <= to_date(:eday,'yyyy-mm-dd hh24:mi:ss') and ku = :ku order by fday desc", prams, "Sbyday"));
        
        }
        public DataSet fccd(string cday, string ku)
        {
            string btime = "";
            string etime = "";
            
            DateTime jstime = Convert.ToDateTime(cday).AddHours(20);
            DateTime kstime = jstime.AddDays(-1);
            btime = kstime.ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":btime", OracleType.VarChar, btime),
                DALHelper.MakeInParam(":etime", OracleType.VarChar, etime),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            return (DALHelper.RunProcReturn(@"select count(*) as cs, sum(dun) as fcds from dd.FC where fday >= to_date(:btime,'yyyy-mm-dd hh24:mi:ss') and fday <= to_date(:etime,'yyyy-mm-dd hh24:mi:ss') and ku = :ku and fcy = '装载'", prams, "fcqk"));
        
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "fc" + time.ToString();


            return num;
        }
    }
}
